package dal;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import dal.PoolConnection;
import entities.Probabilidad;
import entities.Request;
import entities.TextValue;

public class BayesDAL{

	private static BayesDAL instancia;
	
	private BayesDAL(){
		
	}
	
	public static BayesDAL getInstance(){
		if (instancia == null)
			instancia = new BayesDAL();
		return instancia;
	}

	public List<TextValue> getOcupacion()
	{
		List<TextValue> ocupaciones = new ArrayList<TextValue>();
		try
		{
			Connection con = PoolConnection.getPoolConnection().getConnection();
			PreparedStatement ps = con.prepareStatement("select id, descripcion from dbo.ocupacion");
			ResultSet result = ps.executeQuery();
			PoolConnection.getPoolConnection().realeaseConnection(con);
			while (result.next())
			{
				TextValue textvalue = new TextValue(result.getInt(1),result.getString(2));
				ocupaciones.add(textvalue);
			}
			
		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		
		return ocupaciones;
	}
	
	public List<TextValue> getEstadoCivil()
	{
		List<TextValue> list = new ArrayList<TextValue>();
		try
		{
			Connection con = PoolConnection.getPoolConnection().getConnection();
			PreparedStatement ps = con.prepareStatement("select id, descripcion from dbo.estadoCivil");
			ResultSet result = ps.executeQuery();
			PoolConnection.getPoolConnection().realeaseConnection(con);
			while (result.next())
			{
				TextValue textvalue = new TextValue(result.getInt(1),result.getString(2));
				list.add(textvalue);
			}
			
		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		
		return list;
	}

	public List<TextValue> getNiveleducacion()
	{
		List<TextValue> list = new ArrayList<TextValue>();
		try
		{
			Connection con = PoolConnection.getPoolConnection().getConnection();
			PreparedStatement ps = con.prepareStatement("select id, descripcion from dbo.nivelEducacion");
			ResultSet result = ps.executeQuery();
			PoolConnection.getPoolConnection().realeaseConnection(con);
			while (result.next())
			{
				TextValue textvalue = new TextValue(result.getInt(1),result.getString(2));
				list.add(textvalue);
			}
			
		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		
		return list;
	}

	public List<TextValue> getTieneDeuda()
	{
		List<TextValue> list = new ArrayList<TextValue>();
		try
		{
			Connection con = PoolConnection.getPoolConnection().getConnection();
			PreparedStatement ps = con.prepareStatement("select id, descripcion from dbo.adeudaCredito");
			ResultSet result = ps.executeQuery();
			PoolConnection.getPoolConnection().realeaseConnection(con);
			while (result.next())
			{
				TextValue textvalue = new TextValue(result.getInt(1),result.getString(2));
				list.add(textvalue);
			}
			
		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		
		return list;
	}

	public List<TextValue> getTienePrestamoPersonales()
	{
		List<TextValue> list = new ArrayList<TextValue>();
		try
		{
			Connection con = PoolConnection.getPoolConnection().getConnection();
			PreparedStatement ps = con.prepareStatement("select id, descripcion from dbo.prestamosPresonales");
			ResultSet result = ps.executeQuery();
			PoolConnection.getPoolConnection().realeaseConnection(con);
			while (result.next())
			{
				TextValue textvalue = new TextValue(result.getInt(1),result.getString(2));
				list.add(textvalue);
			}
			
		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		
		return list;
	}

	public List<TextValue> getTienePrestamoHipotecarios()
	{
		List<TextValue> list = new ArrayList<TextValue>();
		try
		{
			Connection con = PoolConnection.getPoolConnection().getConnection();
			PreparedStatement ps = con.prepareStatement("select id, descripcion from dbo.prestamosHipotecarios");
			ResultSet result = ps.executeQuery();
			PoolConnection.getPoolConnection().realeaseConnection(con);
			while (result.next())
			{
				TextValue textvalue = new TextValue(result.getInt(1),result.getString(2));
				list.add(textvalue);
			}
			
		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		
		return list;
	}

	public List<TextValue> getRangoSaldo()
	{
		List<TextValue> list = new ArrayList<TextValue>();
		try
		{
			Connection con = PoolConnection.getPoolConnection().getConnection();
			PreparedStatement ps = con.prepareStatement("select id, descripcion from dbo.saldoCajaAhorro");
			ResultSet result = ps.executeQuery();
			PoolConnection.getPoolConnection().realeaseConnection(con);
			while (result.next())
			{
				TextValue textvalue = new TextValue(result.getInt(1),result.getString(2));
				list.add(textvalue);
			}
			
		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		
		return list;
	}
	
	
	public List<TextValue> getTipoContacto()
	{
		List<TextValue> list = new ArrayList<TextValue>();
		try
		{
			Connection con = PoolConnection.getPoolConnection().getConnection();
			PreparedStatement ps = con.prepareStatement("select id, descripcion from dbo.tipoContacto");
			ResultSet result = ps.executeQuery();
			PoolConnection.getPoolConnection().realeaseConnection(con);
			while (result.next())
			{
				TextValue textvalue = new TextValue(result.getInt(1),result.getString(2));
				list.add(textvalue);
			}
			
		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		
		return list;
	}
	
	public void Insert(Request request)
	{
		
	}
	
	public Probabilidad calcularProbabilidad(Request request) throws SQLException
	{
		Probabilidad p = new Probabilidad();
		
		CallableStatement callableStatement = null;
		Connection con = null;
		String calcular_bayes_sp = "{call calcular_bayes_sp(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
		
		try
		{
			con = PoolConnection.getPoolConnection().getConnection();
			callableStatement = con.prepareCall(calcular_bayes_sp);

			callableStatement.setInt(1, request.plazoFijo?1:0);
			callableStatement.setInt(2, request.edad);
			callableStatement.setInt(3, request.ocupacion);
			callableStatement.setInt(4, request.estadoCivil);
			callableStatement.setInt(5, request.nivelEducacion);
			callableStatement.setInt(6, request.adeudaCuotaCredito?1:0);
			callableStatement.setInt(7, request.tienePrestamoHipotecarios?1:0);
			callableStatement.setInt(8, request.tienePrestamosPersonales?1:0);
			callableStatement.setInt(9, request.tipoContacto);
			callableStatement.setInt(10, request.rangoSaldo);
			
			callableStatement.registerOutParameter(11, java.sql.Types.DECIMAL);
			callableStatement.registerOutParameter(12, java.sql.Types.DECIMAL);
			callableStatement.registerOutParameter(13, java.sql.Types.DECIMAL);
			callableStatement.registerOutParameter(14, java.sql.Types.DECIMAL);
			callableStatement.registerOutParameter(15, java.sql.Types.DECIMAL);
			callableStatement.registerOutParameter(16, java.sql.Types.DECIMAL);
			callableStatement.registerOutParameter(17, java.sql.Types.DECIMAL);
			callableStatement.registerOutParameter(18, java.sql.Types.DECIMAL);
			callableStatement.registerOutParameter(19, java.sql.Types.DECIMAL);
			callableStatement.registerOutParameter(20, java.sql.Types.DECIMAL);
			
			callableStatement.executeUpdate();
			PoolConnection.getPoolConnection().realeaseConnection(con);
			
			p.plazoFijo = callableStatement.getDouble(11);
			p.edad = callableStatement.getDouble(12);
			p.ocupacion = callableStatement.getDouble(13);
			p.estadoCivil = callableStatement.getDouble(14);
			p.adeudaCuotaCredito = callableStatement.getDouble(15);
			p.tienePrestamoHipotecarios = callableStatement.getDouble(16);
			p.tienePrestamoPersonales = callableStatement.getDouble(17);
			p.tipoContacto = callableStatement.getDouble(18);
			p.nivelEducacion = callableStatement.getDouble(19);
			p.rangoSaldo = Double.parseDouble(callableStatement.getString(20));
						
		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		 finally 
		 {
		 
			if (callableStatement != null) {
				callableStatement.close();
			}	 
		}
		
		return p;
	}
	
	public List<TextValue> getRangoEdad()
	{
		List<TextValue> list = new ArrayList<TextValue>();
		try
		{
			Connection con = PoolConnection.getPoolConnection().getConnection();
			PreparedStatement ps = con.prepareStatement("select id, descripcion from dbo.edad");
			ResultSet result = ps.executeQuery();
			PoolConnection.getPoolConnection().realeaseConnection(con);
			while (result.next())
			{
				TextValue textvalue = new TextValue(result.getInt(1),result.getString(2));
				list.add(textvalue);
			}
			
		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		
		return list;
	}
	
	public void insertData(Request request) throws SQLException
	{

		CallableStatement callableStatement = null;
		Connection con = null;
		String insert_banco_sp = "{call insert_banco_sp(?,?,?,?,?,?,?,?,?,?)}";

		try
		{			
			con = PoolConnection.getPoolConnection().getConnection();
			callableStatement = con.prepareCall(insert_banco_sp);

			callableStatement.setInt(1, request.edad);
			callableStatement.setInt(2, request.ocupacion);
			callableStatement.setInt(3, request.estadoCivil);
			callableStatement.setInt(4, request.nivelEducacion);
			callableStatement.setInt(5, request.adeudaCuotaCredito?1:0);
			callableStatement.setInt(6, request.rangoSaldo);
			callableStatement.setInt(7, request.tienePrestamoHipotecarios?1:0);
			callableStatement.setInt(8, request.tienePrestamosPersonales?1:0);
			callableStatement.setInt(9, request.tipoContacto);
			callableStatement.setInt(10, request.plazoFijo?1:0);
			
			callableStatement.executeUpdate();
			PoolConnection.getPoolConnection().realeaseConnection(con);
			
			System.out.println("Inserto!!! :D" );
			
		}
		catch (Exception ex)
		{
			ex.printStackTrace();
		}
		 finally 
		 {
		 
			if (callableStatement != null) {
				callableStatement.close();
			}	 
		}
		
		
	}
}
